﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.IO;
using Microsoft.Office.Interop;

namespace WindowsFormsApplication1
{
    public partial class weeklyreport : Form
    {
      //  string[] tablename={"区域","期初","年度发货预算额","当月已发货额(万元)","累计发货(万元)","累计完成年预算比","年度回款预算额","当月已回款额(万元)","累计回款(万元)","累计完成年预算比","应收余额(含预收)"};
        DAL sqlhelp = new DAL();
        DataTable u8 = new DataTable();
        public weeklyreport(string name)
        {
            InitializeComponent();
            dgv_data.DataSource = null;
            dgv_data.AutoGenerateColumns = false;
            SetDateTimePickerFormat(this.dtp_begin);
            SetDateTimePickerFormat(this.dtp_end);
            lb_name.Text = name;
            BindWeek();
        }

        private void btn_extract_Click(object sender, EventArgs e)
        {
                bool result = false;
                this.btn_extract.Enabled = false;
                this.btn_import.Enabled = false;
                this.btn_OutExcel.Enabled = false;
                this.btn_update.Enabled = false;
                string begin = this.dtp_begin.Text;
                string end = this.dtp_end.Text;
                result = Binddgvdata(begin,end);
                dgv_data.ReadOnly = true;
                if (!result)
                {
                    MessageBox.Show("查询失败");
                }
                MessageBox.Show("抽取数据成功！");
                this.btn_extract.Enabled = true;
                this.btn_import.Enabled = true;
                this.btn_OutExcel.Enabled = true;
                this.btn_update.Enabled = true;
        }
      
        //绑定数据源
        public  bool  Binddgvdata(string begin,string  end)
        {
            bool  result = false;
            string ssql = "Cust_usp_tlWeeklyReport";
            SqlParameter[] pms = { new SqlParameter("@begin",begin),new SqlParameter("@end",end) };
            DataSet ds = sqlhelp.ExtractData(ssql, pms);
        /*    foreach(DataRow dr in dt.Rows)
            {

                if (dr != null) {
                    dgv_data.Rows.Add(dr);
                    dgv_data.Rows[count].Cells[0].Value = dr[0];
                  //  MessageBox.Show(dr[0].ToString());
                    count++;
                }
            }
           */
            if (ds != null)
            {
                u8 = ds.Tables["f_tlweeklyreport"];
                DataTable bi = ds.Tables["f_yearplan"];
                for (int i = 0; i <u8.Rows.Count; i++) 
                {
                    for(int k=0;k<bi.Rows.Count;k++)
                    {
                        if (u8.Rows[i][0].ToString().Substring(0, 4) == bi.Rows[k][0].ToString().Substring(0, 4) && u8.Rows[i][1].ToString().Equals(bi.Rows[k][1].ToString())) 
                        {
                            //--test
                            string fout_lj = u8.Rows[i]["fout_lj"].ToString();
                            string fin_lj = u8.Rows[i]["fin_lj"].ToString();
                            string fyearplanin = bi.Rows[k]["年度回款预算额"].ToString();
                            string fyearplanout = bi.Rows[k]["年度发货预算额"].ToString();

                            //---
                            u8.Rows[i]["fbegin"]=bi.Rows[i]["期初"];
                            u8.Rows[i]["fyearplanin"]=bi.Rows[k]["年度回款预算额"];
                            u8.Rows[i]["fyearplanout"]=bi.Rows[k]["年度发货预算额"];
                            if (u8.Rows[i]["fyearplanout"].ToString() == "" || u8.Rows[i]["fyearplanout"] == null || Convert.ToInt32(u8.Rows[i]["fyearplanout"]).Equals(0))
                            {
                                u8.Rows[i]["ffinishoutrate"]=0;
                            }
                            else
                            {
                               // u8.Rows[i]["ffinishoutrate"] =Math.Round(decimal.Parse(u8.Rows[i]["fout_lj"].ToString())/decimal.Parse(u8.Rows[i]["fyearplanout"].ToString()),6);
                                u8.Rows[i]["ffinishoutrate"] = Math.Round(Convert.ToDecimal(u8.Rows[i]["fout_lj"]) / Convert.ToDecimal(u8.Rows[i]["fyearplanout"]),4);
                            }
                            if (u8.Rows[i]["fyearplanin"].ToString() == "" || u8.Rows[i]["fyearplanin"] == null || Convert.ToInt32(u8.Rows[i]["fyearplanin"]).Equals(0))
                            {
                                u8.Rows[i]["ffinishinrate"]=0;
                            }
                            else
                            {
                               // u8.Rows[i]["ffinishinrate"]=Math.Round(decimal.Parse(u8.Rows[i]["fin_lj"].ToString())/decimal.Parse(u8.Rows[i]["fyearplanin"].ToString()),6);
                                u8.Rows[i]["ffinishinrate"] = Math.Round(Convert.ToDecimal(u8.Rows[i]["fin_lj"])/Convert.ToDecimal(u8.Rows[i]["fyearplanin"]), 4);
                            }
                            u8.Rows[i]["fye"] =Math.Round(Convert.ToDecimal(u8.Rows[i]["fbegin"])+Convert.ToDecimal(u8.Rows[i]["fout_lj"]) -Convert.ToDecimal(u8.Rows[i]["fin_lj"]),2);
                        }
                    }
                    
                }

                    dgv_data.DataSource = u8;
                result = true;
            }
            return result;
        }

        private bool SetDateTimePickerFormat(params DateTimePicker[] dtp)
        {
            try
            {
                if (dtp != null)
                {
                    foreach (var item in dtp)
                    {
                        item.Format = DateTimePickerFormat.Custom;
                        item.CustomFormat = "yyyy-MM-dd";
                    }
                }
            }
            catch
            {
                return false;
            }
            return true;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            lb_now.Text = DateTime.Now.ToString();
        }

        private void btn_import_Click(object sender, EventArgs e)
        {
            /*string filepath = System.AppDomain.CurrentDomain.BaseDirectory+"\\特缆销售报表.xlsx";
            DataSet ds = new DataSet();
            DialogResult result =  MessageBox.Show("是否使用默认路径文件？","操作",MessageBoxButtons.YesNo,MessageBoxIcon.Question);
            if (result == DialogResult.No)
            {
                OpenFileDialog filefrm = new OpenFileDialog();
                filefrm.Filter = "Excel2003文件|*.xls|Excel2007/2010|*.xlsx";
                if (filefrm.ShowDialog() == DialogResult.OK)
                {
                    if (filefrm.FileName != "" || filefrm.FileName != null)
                    {
                        filepath = filefrm.FileName;
                    }
                }
                else 
                {
                    return;
                }
            }
            ds = sqlhelp.ExtractExcel(filepath);
            sqlhelp.WriteExcel(ds);*/
            if (dgv_data.RowCount > 0)
            {
                DialogResult dresult = MessageBox.Show("确定导入数据到BI？", "询问", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                if (dresult == DialogResult.Yes)
                {
                    bool result = sqlhelp.ImportDataToBI(u8,dtp_end.Text,cbb_week.Text);
                    if (result)
                    {
                        MessageBox.Show("导入成功！");
                    }
                    else
                    {
                        MessageBox.Show("导入失败！");
                    }
                }
            }
            else 
            {
                MessageBox.Show("查询结果为空！请重新查询");
            }
        }

        private void btn_OutExcel_Click(object sender, EventArgs e)
        {
            if (dgv_data.RowCount > 1)
            {
                string saveFilename = "";
                SaveFileDialog saveDialog = new SaveFileDialog();
                saveDialog.DefaultExt = "xls";
                saveDialog.Filter = "Excel2003文件|*.xls|Excel2007/2010|*.xlsx";
                saveDialog.FileName = "";
                if (saveDialog.ShowDialog() == DialogResult.Cancel)
                {
                    return;
                }
                saveFilename = saveDialog.FileName;
                Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
                if (xlApp == null)
                {
                    MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
                    return;
                }
                Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
                Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
                Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];
                //写入标题
                for (int i = 0; i < dgv_data.ColumnCount - 1; i++)
                {
                    worksheet.Cells[1, i + 1] = dgv_data.Columns[i].HeaderText;
                }
                //写入数值
                for (int j = 0; j < dgv_data.Rows.Count; j++)
                {
                    for (int k = 0; k < dgv_data.ColumnCount - 1; k++)
                    {
                        worksheet.Cells[j + 2, k + 1] = dgv_data.Rows[j].Cells[k].Value;
                    }
                    System.Windows.Forms.Application.DoEvents();
                }
                worksheet.Columns.EntireColumn.AutoFit();
                if (saveFilename != "")
                {
                    try
                    {
                        workbook.Saved = true;
                        workbook.SaveCopyAs(saveFilename);
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show("导出文件时出错,文件可能正被打开！\n" + ex.Message);
                    }
                }
                xlApp.Quit();
                GC.Collect();//强行销毁
                MessageBox.Show("文件：" + "特缆销售周报" + ".xls 保存成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else 
            {
                MessageBox.Show("查询结果为空！请重新查询");
            }
        }

      

        private void btn_update_Click(object sender, EventArgs e)
        {
            if (dgv_data.RowCount > 0)
            {
                if (btn_update.Text == "手动修改")
                {
                    DialogResult result = MessageBox.Show("确定修改数据？修改后不可还原", "询问", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
                    if (result == DialogResult.Yes)
                    {
                        btn_update.Text = "(正在修改)确定";
                        dgv_data.ReadOnly = false;
                        btn_extract.Enabled = false;
                        btn_import.Enabled = false;
                        btn_OutExcel.Enabled = false;
                    }

                }
                else if (btn_update.Text == "(正在修改)确定")
                {
                    btn_update.Text = "手动修改";
                    dgv_data.ReadOnly = true;
                    btn_extract.Enabled = true;
                    btn_import.Enabled = true;
                    btn_OutExcel.Enabled = true;
                }
            }
            else 
            {
                MessageBox.Show("查询结果为空！请重新查询");
            }
        }

        /// <summary>
        /// 退出系统
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void weeklyreport_FormClosing(object sender, FormClosingEventArgs e)
        {
           
            DialogResult result = MessageBox.Show("是否退出系统？", "询问", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
            if (result == DialogResult.Yes)
            {
                this.Dispose();
                //e.Cancel = false;
                Application.Exit();
            }
            else
            {
                e.Cancel = true;
            }
        }

        private void dgv_data_CellMouseEnter(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                dgv_data.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.LightBlue;
            }
        }

        private void dgv_data_CellMouseLeave(object sender, DataGridViewCellEventArgs e)
        {
            if (e.RowIndex >= 0)
            {
                dgv_data.Rows[e.RowIndex].DefaultCellStyle.BackColor = Color.White;
            }
        }
       

        private void BindWeek() 
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("id");
            dt.Columns.Add("name");
            for (int i = 1; i < 5; i++) 
            {
                DataRow dr = dt.NewRow();
                dr["id"] = i;
                switch (i) 
                {
                    case 1: dr["name"] = "第一周"; break;
                    case 2: dr["name"] = "第二周"; break;
                    case 3: dr["name"] = "第三周"; break;
                    case 4: dr["name"] = "第四周"; break;
                    default: dr["name"] = "第一周"; break;
                }
                dt.Rows.Add(dr);
            }
            cbb_week.DataSource = dt;
            cbb_week.DisplayMember = "name";
            cbb_week.ValueMember = "id";
        }

    }
   
}
